package cn.edu.sdjzu.xg.kcsj.dao;

import cn.edu.sdjzu.xg.kcsj.domain.Classroom;
import cn.edu.sdjzu.xg.kcsj.domain.Course;
import cn.edu.sdjzu.xg.kcsj.domain.Student;
import cn.edu.sdjzu.xg.kcsj.domain.StudentSign;
import cn.edu.sdjzu.xg.kcsj.service.ClassroomService;
import cn.edu.sdjzu.xg.kcsj.service.CourseService;
import cn.edu.sdjzu.xg.kcsj.service.StudentService;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;

public class StudentSignDao {
    private static StudentSignDao studentSignDao = null;
    private StudentSignDao() {
    }
    public static StudentSignDao getInstance() {
        if (studentSignDao == null) {
            studentSignDao = new StudentSignDao ();
        }
        return studentSignDao;
    }

    public Collection<StudentSign> findAll (Connection connection, List<Condition> conditionList, Pagination pagination) throws SQLException {
        //创建StudentSign类型集合类对象，HashSet不保证结果中元素的顺序
        Collection<StudentSign> studentSigns = new HashSet<StudentSign>();
        //创建查询学生签到的主语句
        StringBuilder sql_select = new StringBuilder("SELECT * FROM studentsign");
        //如果有条件，则生成条件子句，并附加到主语句后面
        if (conditionList != null) {
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
        }
        //获取StudentSign中的元组总数
        int totalNum = CommonDao.getInstance().count(connection, "studentsign");
        if (pagination != null) {
            String paginationClause = pagination.toLimitClause(totalNum);
            sql_select.append(paginationClause);
        }
        //根据连接对象准备语句对象，如果SQL语句为多行，注意语句不同部分之间要有空格
        PreparedStatement pstmt_select_student =
                connection.prepareStatement(sql_select.toString());
        //执行预编译语句
        ResultSet resultSet_studentSign = pstmt_select_student.executeQuery();
        while (resultSet_studentSign.next ()){
            int id = resultSet_studentSign.getInt ("id");
            String no =  resultSet_studentSign.getString ("no");
            int course_id = resultSet_studentSign.getInt ("course_id");
            Course course = CourseService.getInstance ().find (course_id);
            String signState = resultSet_studentSign.getString ("signState");
            String week = resultSet_studentSign.getString ("week");
            String weekTime = resultSet_studentSign.getString("weekTime");
            String section = resultSet_studentSign.getString ("section");
            int classroom_id = resultSet_studentSign.getInt ("classroom_id");
            Classroom classroom = ClassroomService.getInstance ().find (classroom_id);
            int student_id = resultSet_studentSign.getInt ("student_id");
            Student student = StudentService.getInstance ().find (student_id);
            studentSigns.add (new StudentSign (id,no,course,signState,week,weekTime,section,classroom,student));
        }
        JdbcHelper.close (resultSet_studentSign,pstmt_select_student);
        return studentSigns;
    }

    public StudentSign find (int id_int,Connection connection) throws SQLException {
        StudentSign studentSign = null;
        String find_sql = "select * from studentsign where id = ?";
        PreparedStatement pstm_studentSign = connection.prepareStatement (find_sql);
        pstm_studentSign.setInt (1,id_int);
        ResultSet resultSet_studentSign = pstm_studentSign.executeQuery ();
        if (resultSet_studentSign.next ()){
            String no =  resultSet_studentSign.getString ("no");
            int course_id = resultSet_studentSign.getInt ("course_id");
            Course course = CourseService.getInstance ().find (course_id);
            String signState = resultSet_studentSign.getString ("signState");
            String week = resultSet_studentSign.getString ("week");
            String weekTime = resultSet_studentSign.getString("weekTime");
            String section = resultSet_studentSign.getString ("section");
            int classroom_id = resultSet_studentSign.getInt ("classroom_id");
            Classroom classroom = ClassroomService.getInstance ().find (classroom_id);
            int student_id = resultSet_studentSign.getInt ("student_id");
            Student student = StudentService.getInstance ().find (student_id);
            studentSign = new StudentSign (no,course,signState,week,weekTime,section,classroom,student);
        }
        JdbcHelper.close (resultSet_studentSign,pstm_studentSign);
        return studentSign;
    }

    public Boolean add (StudentSign studentSignToAdd,Connection connection) throws SQLException {

        String add_sql = "INSERT INTO studentsign(no,course_id,signState,week,weekTime,section,classroom_id,student_id) VALUES (?,?,?,?,?,?,?,?)";
        PreparedStatement pstm_toAdd = connection.prepareStatement (add_sql);
        pstm_toAdd.setString (1,studentSignToAdd.getNo ());
        pstm_toAdd.setInt (2,studentSignToAdd.getCourse ().getId ());
        pstm_toAdd.setString (3,studentSignToAdd.getSignState ());
        pstm_toAdd.setString (4,studentSignToAdd.getWeek ());
        pstm_toAdd.setString (5,studentSignToAdd.getWeekTime ());
        pstm_toAdd.setString (6,studentSignToAdd.getSection ());
        pstm_toAdd.setInt (7,studentSignToAdd.getClassroom ().getId ());
        pstm_toAdd.setInt (8,studentSignToAdd.getStudent ().getId ());
        //获取执行结果
        int affectedRowNum = pstm_toAdd.executeUpdate();
        //根据结果，具体判断所要返回的值
        if (affectedRowNum > 0) {
            JdbcHelper.close(null,pstm_toAdd);
            return true;
        } else {
            JdbcHelper.close (null,pstm_toAdd);
            return false;
        }
    }

    public Boolean update (StudentSign studentSignToUpdate,Connection connection) throws SQLException {
        String update_sql = "UPDATE studentsign SET no=?,course_id=?,signState=?,week=?,weekTime=?,section=?,classroom_id=?,student_id=? where id = ?";
        PreparedStatement pstm_toAdd = connection.prepareStatement (update_sql);
        pstm_toAdd.setString (1,studentSignToUpdate.getNo ());
        pstm_toAdd.setInt (2,studentSignToUpdate.getCourse ().getId ());
        pstm_toAdd.setString (3,studentSignToUpdate.getSignState ());
        pstm_toAdd.setString (4,studentSignToUpdate.getWeek ());
        pstm_toAdd.setString (5,studentSignToUpdate.getWeekTime ());
        pstm_toAdd.setString (6,studentSignToUpdate.getSection ());
        pstm_toAdd.setInt (7,studentSignToUpdate.getClassroom ().getId ());
        pstm_toAdd.setInt (8,studentSignToUpdate.getStudent ().getId ());
        pstm_toAdd.setInt (9,studentSignToUpdate.getId());
        //获取执行结果
        int affectedRowNum = pstm_toAdd.executeUpdate();
        //根据结果，具体判断所要返回的值
        JdbcHelper.close(null,pstm_toAdd);
        if (affectedRowNum > 0) {
            return true;
        } else {
            return false;
        }
    }

    public Boolean delete (int id,Connection connection) throws SQLException {
        String delete_sql = "DELETE FROM studentsign WHERE id = ?";
        PreparedStatement pstm_delete = connection.prepareStatement (delete_sql);
        pstm_delete.setInt (1,id);
        if (pstm_delete.executeUpdate () > 0){
            JdbcHelper.close(null,pstm_delete);
            return true;
        }else{
            JdbcHelper.close(null,pstm_delete);
            return false;
        }
    }
}
